﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using nplJobsCommon.Common;
using nplJobs.DB;
using System.Data;
using nplJobsCommon.JobSeeker;
using System.Data.SqlClient;

namespace nplJobsDAL.Common
{
    /// <summary>
    /// Select educational info from the database
    /// </summary>
    public class SelectEducationalInformation : DataAccessBase
    {

        public Int64 EducationalInfoID { get; set; }
        public Guid JobseekerID { get; set; }


        public SelectEducationalInformation()
        {
            StoredProcedureName = StoredProcedure.Name.spSelectEducationalInfoByID.ToString();
        }

        #region GetEducationalInfo
        public List<EducationInfo> GetEducationalInfo()
        {
            DataBaseHelper dbHelper;
            DataSet dsEducationalInfo;

            try
            {
                SelectEducationalInfoParameters selectEducationalInfoParameters = new SelectEducationalInfoParameters(this.EducationalInfoID, this.JobseekerID);
                dbHelper = new DataBaseHelper(StoredProcedureName);
                dbHelper.Parameters = selectEducationalInfoParameters.Parameters;
                dsEducationalInfo = dbHelper.Run(base.ConnectionString, dbHelper.Parameters);
                return GetList(dsEducationalInfo);
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region SQL parameters for selecting educational info by id
        public class SelectEducationalInfoParameters
        {
            private SqlParameter[] _parameters;

            public SelectEducationalInfoParameters(Int64 educationalInfoID,Guid JobseekerId)
            {
                this.EducationalInfoID = educationalInfoID;
                this.JobseekerID = JobseekerId;
                Build();
            }

            #region spAddContact parameters
            /*
         
         */
            #endregion

            private void Build()
            {
                SqlParameter[] parameters = 
                {
                    new SqlParameter("@EducationalInfoID", EducationalInfoID),
                    new SqlParameter("@JobseekerID", JobseekerID),

                };

                Parameters = parameters;
            }

            public Int64 EducationalInfoID { get; set; }
            public Guid JobseekerID { get; set; }


            public SqlParameter[] Parameters
            {
                get { return _parameters; }
                set { _parameters = value; }
            }
        }
        #endregion

        #region GetList
        private List<EducationInfo> GetList(DataSet dsEducationalInfo)
        {
            List<EducationInfo> educationalinfo;
            try
            {
                educationalinfo = (from n in dsEducationalInfo.Tables[0].AsEnumerable()
                                   select new EducationInfo
                                    {
                                        JobSeekerID = n.Field<Guid>("JobSeekerID"),
                                        EducationInfoID = n.Field<Int64>("EducationInfoID"),
                                        InstitutionName = n.Field<string>("InstitutionName"),
                                        FieldOfStudyID = n.Field<int>("FieldOfStudyID"),
                                        SpecializedSubjects = n.Field<string>("SpecializedSubjects"),
                                        Degree = n.Field<string>("Degree"),
                                        Grade = n.Field<string>("Grade"),
                                        FromDate = n.Field<DateTime?>("FromDate"),
                                        ToDate = n.Field<DateTime?>("ToDate"),
                                    }).ToList();

                    return educationalinfo;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally { }
        }
        #endregion
    }
}

